package org.cadet.client.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.cadet.client.bean.BeanThought;
import org.cadet.client.model.UserManagement;
import org.cadet.util.model.Constants;
import org.cadet.util.model.DatabaseConnection;

/**
 * @author Nagmani
 *
 */

public class ThoughtManagement {

	DatabaseConnection objConnection = DatabaseConnection.getInstance();

	public void addThought(BeanThought thought) throws SQLException {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(Constants.sqlCommands.AddThought);
		pst.setInt(1, thought.getAuthorId());
		pst.setString(2, thought.getThoughtContent());
		pst.setString(3, thought.getThoughtTitle());
		pst.executeUpdate();
		pst.close();
	}

	public void editThought(BeanThought thought) throws SQLException,
			Exception {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(Constants.sqlCommands.UpdateThought);
		pst.setInt(1, thought.getAuthorId());
		pst.setString(2, thought.getThoughtDate());
		pst.setString(3, thought.getThoughtContent());
		pst.setString(4, thought.getThoughtTitle());
		pst.setInt(5, thought.getThoughtId());
		pst.executeUpdate();
		pst.close();
	}

	public void removeThought(int thoughtId) throws SQLException {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(Constants.sqlCommands.RemoveThought);
		pst.setInt(1, thoughtId);
		pst.executeUpdate();
		pst.close();
	}

	public ArrayList<BeanThought> viewAllThought() throws Exception  {
		Statement st = objConnection.getDbConnection().createStatement();
		ResultSet rs = st
				.executeQuery(Constants.sqlCommands.RetrieveAllThought);
		ArrayList<BeanThought> thoughtList = new ArrayList<BeanThought>();
		BeanThought thought;
		UserManagement user = new UserManagement();

		while (rs.next()) {
			thought = new BeanThought();
			thought.setThoughtId(Integer.parseInt(rs.getString("ID")));
			thought.setAuthorId(Integer.parseInt(rs.getString("post_author")));
			thought.setThoughtDate(rs.getString("post_date"));
			thought.setThoughtContent(rs.getString("post_content"));
			thought.setThoughtTitle(rs.getString("post_title"));
			thought.setAuthorName(user.getDisplayNameById(thought.getAuthorId()));
			thoughtList.add(thought);
		}
		return thoughtList;
	}

	public ArrayList<BeanThought> viewFollowedUserThought(int userId) throws Exception  {
		Statement st = objConnection.getDbConnection().createStatement();
		UserManagement user = new UserManagement();
		String followedUserIds = user.FollowedIds(userId);
		ResultSet rs = st
				.executeQuery(Constants.sqlCommands.RetrieveFollowedUserThought + "(" + followedUserIds + ") ORDER BY post_date DESC");
		ArrayList<BeanThought> thoughtList = new ArrayList<BeanThought>();
		BeanThought thought;

		while (rs.next()) {
			thought = new BeanThought();
			thought.setThoughtId(Integer.parseInt(rs.getString("ID")));
			thought.setAuthorId(Integer.parseInt(rs.getString("post_author")));
			thought.setThoughtDate(rs.getString("post_date"));
			thought.setThoughtContent(rs.getString("post_content"));
			thought.setThoughtTitle(rs.getString("post_title"));
			thought.setAuthorName(user.getDisplayNameById(thought.getAuthorId()));
			thoughtList.add(thought);
		}
		return thoughtList;
	}

	
	public BeanThought viewThoughtByThoughtId(int thoughtId)
			throws Exception {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveThoughtByThoughtID);
		pst.setInt(1, thoughtId);
		ResultSet rs = pst.executeQuery();
		BeanThought thought = new BeanThought();
		if (rs.next()) {
			thought = new BeanThought();
			thought.setThoughtId(Integer.parseInt(rs.getString(1)));
			thought.setAuthorId(Integer.parseInt(rs.getString(2)));
			thought.setThoughtDate(rs.getString(3));
			thought.setThoughtContent(rs.getString(4));
			thought.setThoughtTitle(rs.getString(5));
			thought.setAuthorName(new UserManagement().getDisplayNameById(thought.getAuthorId()));
		}
		return thought;
	}

	public ArrayList<BeanThought> viewAuthorwiseThought(int authorId)
			throws Exception {
		PreparedStatement pst = objConnection.getDbConnection()
				.prepareStatement(
						Constants.sqlCommands.RetrieveAuthorwiseThought);
		pst.setInt(1, authorId);
		ResultSet rs = pst.executeQuery();
		ArrayList<BeanThought> thoughtList = new ArrayList<BeanThought>();
		BeanThought thought;
		UserManagement user = new UserManagement();

		while (rs.next()) {
			thought = new BeanThought();
			thought.setThoughtId(Integer.parseInt(rs.getString(1)));
			thought.setAuthorId(Integer.parseInt(rs.getString(2)));
			thought.setThoughtDate(rs.getString(3));
			thought.setThoughtContent(rs.getString(4));
			thought.setThoughtTitle(rs.getString(5));
			thought.setAuthorName(user.getDisplayNameById(thought.getAuthorId()));
			thoughtList.add(thought);
		}
		return thoughtList;
	}	
} 